-- 1.查当前规划到达时间内的经停网点信息
-- SELECT *
-- FROM YL_TMSNEW_TMS_SHIPMENT_STOP
-- WHERE NETWORK_CODE in
-- (SELECT code
-- FROM YL_LMDM_SYS_NETWORK
-- WHERE code in (
-- SELECT code FROM YL_LMDM_SYS_NETWORK START WITH code = #{networkCode}  CONNECT BY PRIOR code = PARENT_NETWORK_CODE ) and TYPE_ID = 335)
--  and PLANNED_ARRIVAL_TIME >= TO_DATE(#{startTime},'yyyy-MM-dd HH24:mi:ss')
--  and PLANNED_ARRIVAL_TIME <= TO_DATE(#{endTime},'yyyy-MM-dd HH24:mi:ss')
--
-- 2.基于shipmentId 查询 YL_TMSNEW_TMS_SHIPMENT  过滤掉状态 是已作废的任务单
-- 3.基于  查询 YL_TMSNEW_TMS_SHIPMENT 里的 carrierid 查询 YL_TMS_CARRIER 获取承运商简称 carrierShortName
-- 4.组装数据 主要是YL_TMSNEW_TMS_SHIPMENT_STOP表的数据
--     1. 查询票数信息
--      SELECT * FROM TAB_REPORT_LOAD_UNLOAD_GX where SHIPMENT_NO =  #{shipmentNo}
--     2.汇总所有当前站点之前的 装车扫描 和 卸车扫描，装车扫描减去卸车扫描 就是到达当前站点的票数  当前站点的卸车扫描就是卸车票数。

with tab_first as (
    select * from (
        select *,row_number() over(partition by code order by tag asc,update_time desc) as row_id
        from (
            select
                code
                ,dest_center_code
                ,dest_center_name
                ,1 as tag
                ,update_time
            from jms_dim.dim_lmdm_sys_first_code
            where is_enable = 1 and is_delete = 1
            union all

            select
                t1.express_center_short_code as code
                ,t1.express_center_code as dest_center_code
                ,t2.name as dest_center_name
                ,2 as tag
                ,t1.update_time
            from jms_dim.dim_yl_sys_package_different_base t1
            left join jms_dim.dim_network_whole_massage t2
            on t1.express_center_code = t2.code
        ) tmp_union
    ) tmp_row
    where row_id = 1
)

insert overwrite table jms_dm.dm_center_advance_prediction_detail_hi partition(dt)
select
     shipment_no
    ,shipment_name
    ,billcode
    ,max(start_site_sort_num)  as start_site_sort_num        -- 始发站点顺序编号
    ,max(start_site_code)      as start_site_code            -- 始发站点编号
    ,max(start_site_name)      as start_site_name            -- 始发站点名称
    ,max(load_scan_time)       as load_scan_time             -- 装车扫描时间
    ,max(load_next_site_code)  as load_next_site_code        -- 装车下一站编码
    ,max(load_next_site_name)  as load_next_site_name        -- 装车下一站名称
    ,max(end_site_code)        as end_site_code              -- 目的站点编号
    ,max(end_site_name)        as end_site_name              -- 目的站点名称
    ,max(unload_scan_time)     as unload_scan_time           -- 卸车扫描时间
    ,max(unload_pre_site_code) as unload_pre_site_code       -- 卸车扫描上一站编码
    ,max(unload_pre_site_name) as unload_pre_site_name       -- 卸车扫描上一站名称
--     ,t_order.first_code                                      -- 一段码
    ,fstcode.dest_center_code as end_center_code             -- 目的中心编码
    ,fstcode.dest_center_name as end_center_name             -- 目的中心名称
    ,if(max(load_next_site_code) = fstcode.dest_center_code ,1,0) as is_transfer_deliver -- 是否中转 1-派件 0-中转
    ,substr(t_road.dt,1,10) as dt
from jms_dwd.dwd_wide_tms_road_waybill_hf t_road
left join jms_dwd.dwd_yl_oms_oms_order_hf t_order
on t_road.billcode = t_order.waybill_id
left join tab_first fstcode
on t_order.first_code = fstcode.code
where t_road.dt >= date_add('{{ execution_date | cst_ds }}', -3) and t_road.dt <= date_add('{{ execution_date | cst_ds }}', 2)
and t_road.is_trunk_branch = 1
and t_order.dt >= date_add('{{ execution_date | cst_ds }}', -7) and t_order.dt <= date_add('{{ execution_date | cst_ds }}', 2)
and t_order.waybill_rnk = 1
group by
     shipment_no
    ,shipment_name
    ,billcode
    ,fstcode.dest_center_code
    ,fstcode.dest_center_name
    ,substr(t_road.dt,1,10)
distribute by dt,pmod(hash(rand()),30);

insert overwrite table jms_tmp.dm_center_advance_prediction_detail_hi
select
     shipment_no
    ,shipment_name
    ,billcode
    ,start_site_sort_num
    ,start_site_code
    ,start_site_name
    ,load_scan_time
    ,load_next_site_code
    ,load_next_site_name
    ,end_site_code
    ,end_site_name
    ,unload_scan_time
    ,unload_pre_site_code
    ,unload_pre_site_name
    ,end_center_code
    ,end_center_name
    ,is_transfer_deliver
from jms_dm.dm_center_advance_prediction_detail_hi
where dt >= date_add('{{ execution_date | cst_ds }}', -3)
and dt <= date_add('{{ execution_date | cst_ds }}', 2);
-- distribute by 20;

insert overwrite table jms_dm.dm_center_advance_prediction_hi partition(dt)
select
    dt as planned_departure_day                          -- 计划发车时间
    ,shipment_no                                         -- 任务号编码
    ,shipment_name                                       -- 任务号名称
    ,count(1) as cnt                                     -- 任务号对应单量
    ,sum(is_transfer_deliver) as deliver_cnt             -- 派件量
    ,count(1) - sum(is_transfer_deliver) as transfer_cnt -- 中转量
    ,dt
from jms_dm.dm_center_advance_prediction_detail_hi
where dt >= date_add('{{ execution_date | cst_ds }}', -3) and dt <= date_add('{{ execution_date | cst_ds }}', 2)
group by
    shipment_no
    ,shipment_name
    ,dt
distribute by dt;


